package com.yskj.mwk.dao;

import com.github.pagehelper.Page;
import com.yskj.mwk.bean.*;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

public interface ManagerMapper {

    //登录管理后台
    @Select("select * from supplier where supplierPhone=#{supplierPhone} and supplierPwd=#{supplierPwd} and supplierType=#{supplierType}")
    Supplier loginMa(@Param("supplierPhone") String supplierPhone, @Param("supplierPwd") String supplierPwd, @Param("supplierType") int supplierType);

    //计算销售额 平台
    @Select("select sum(totalPrice) from orders where payStatus=1 and (orderStatus=2 or orderStatus=3 or orderStatus=4) and createTime LIKE CONCAT(#{nowMonth}, '%')")
    BigDecimal sumTotalPrice(@Param("nowMonth") String nowMonth);

    //计算销售额 租户
    @Select("select sum(totalPrice) from orders where payStatus=1 and (orderStatus=2 or orderStatus=3 or orderStatus=4) and supplierId=#{supplierId} and createTime LIKE CONCAT(#{nowMonth}, '%')")
    BigDecimal sumTotalPriceBySupplierId(@Param("supplierId") int supplierId,@Param("nowMonth") String nowMonth);

    //计算订单量 平台
    @Select("select count(1) from orders where payStatus=1 and (orderStatus=2 or orderStatus=3 or orderStatus=4) and createTime LIKE CONCAT(#{nowMonth}, '%')")
    int sumTotalOrderNum(@Param("nowMonth") String nowMonth);

    //计算订单量 租户
    @Select("select count(1) from orders where payStatus=1 and (orderStatus=2 or orderStatus=3 or orderStatus=4) and supplierId=#{supplierId} and createTime LIKE CONCAT(#{nowMonth}, '%')")
    int sumTotalOrderNumBySupplierId(@Param("supplierId") int supplierId,@Param("nowMonth") String nowMonth);

    //计算每月新增客户数
    @Select("select count(1) from users where  createTime LIKE CONCAT(#{nowMonth}, '%')")
    int sumNewCustomerNum(@Param("nowMonth") String nowMonth);

    //计算每月成本 平台
    @Select("SELECT sum(o.num*g.costPrice) as chengben from orderdesc o LEFT JOIN goods_sku g ON o.skuId=g.skuId LEFT JOIN orders od on od.orderId=o.orderId where (od.orderStatus=2 or od.orderStatus=3 or od.orderStatus=4) and od.createTime LIKE CONCAT(#{nowMonth}, '%')")
    BigDecimal sumTotalChengben(@Param("nowMonth") String nowMonth);

    //计算每月成本 租户
    @Select("SELECT sum(o.num*g.costPrice) as chengben from orderdesc o LEFT JOIN goods_sku g ON o.skuId=g.skuId LEFT JOIN orders od on od.orderId=o.orderId where (od.orderStatus=2 or od.orderStatus=3 or od.orderStatus=4) and od.supplierId=#{supplierId} and od.createTime LIKE CONCAT(#{nowMonth}, '%')")
    BigDecimal sumTotalChengbenBySupplierId(@Param("supplierId") int supplierId,@Param("nowMonth") String nowMonth);

    //获取某月某种订单状态下的订单数量 平台
    @Select("select count(1) from orders where orderStatus=#{orderStatus} and createTime LIKE CONCAT(#{nowMonth}, '%')")
    int totalOrdersNumByOrderStatus(@Param("orderStatus") int orderStatus,@Param("nowMonth") String nowMonth);

    //获取某月某种订单状态下的订单数量 租户
    @Select("select count(1) from orders where supplierId=#{supplierId} and orderStatus=#{orderStatus} and createTime LIKE CONCAT(#{nowMonth}, '%')")
    int totalOrdersNumByOrderStatusAndSupplierId(@Param("supplierId") int supplierId,@Param("orderStatus") int orderStatus,@Param("nowMonth") String nowMonth);

    //获取某个月份某个销量前十的产品 平台
    @Select("SELECT goodsName,spuId, COUNT(*) AS count FROM orderdesc where orderTime LIKE CONCAT(#{nowMonth}, '%') GROUP BY spuId,goodsName ORDER BY count DESC LIMIT 10")
    List<Map<String, Object>> getHighSellingProducts(@Param("nowMonth") String nowMonth);

    //获取某个月份某个销量前十的产品 租户
    @Select("SELECT goodsName,spuId, COUNT(*) AS count FROM orderdesc where supplierId=#{supplierId} and orderTime LIKE CONCAT(#{nowMonth}, '%') GROUP BY spuId,goodsName ORDER BY count DESC LIMIT 10")
    List<Map<String, Object>> getHighSellingProductsBySupplierId(@Param("supplierId") int supplierId,@Param("nowMonth") String nowMonth);

    //获取租户列表
    @Select("SELECT * FROM supplier WHERE supplierName LIKE CONCAT('%',#{supplierName},'%') and supplierType=1 order by createTime desc")
    Page<Supplier> getSuppliersByPage(@Param("supplierName") String supplierName,@Param("pageNum") int pageNum,@Param("pageSize") int pageSize);

    //获取租户列表不分页
    @Select("SELECT * FROM supplier WHERE supplierName LIKE CONCAT('%',#{supplierName},'%') and supplierType=1 order by createTime desc")
    List<Supplier> getAllSuppliers(@Param("supplierName") String supplierName);

    //新增租户
    @Insert("INSERT INTO supplier (supplierName,supplierPhone,supplierPwd,supplierType,createTime,supplierAddress,supplierLxr) VALUES (#{supplier.supplierName},#{supplier.supplierPhone},#{supplier.supplierPwd},#{supplier.supplierType},#{supplier.createTime},#{supplier.supplierAddress},#{supplier.supplierLxr})")
    int addSupplier(@Param("supplier") Supplier supplier);

    //编辑租户
    @Update("update supplier set supplierName=#{supplier.supplierName},supplierPhone=#{supplier.supplierPhone},supplierPwd=#{supplier.supplierPwd},supplierAddress=#{supplier.supplierAddress},supplierLxr=#{supplier.supplierLxr} where supplierId=#{supplier.supplierId}")
    int updateSupplier(@Param("supplier") Supplier supplier);

    //获取租户详情通过id
    @Select("SELECT * FROM supplier WHERE supplierId=#{id}")
    Supplier getSupplierDescById(@Param("id") int id);

    //获取客户列表
    @Select("SELECT A.*,COUNT(B.uuid) AS orderNum,MAX(B.createTime) AS latestTime,SUM(B.totalPrice) AS amount  FROM users A LEFT JOIN orders B ON A.uuid = B.uuid WHERE A.name LIKE CONCAT('%',#{name},'%') and A.tell LIKE CONCAT('%',#{tell},'%') GROUP BY A.id order by A.createTime desc")
    Page<User> getUsersByPage(@Param("name") String name, @Param("tell") String tell, @Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

    //获取订单列表 平台
    @Select("<script>" +
            "select o.*,u.name as uname,u.tell as utell,a.province as receivePro,a.city as receiveCity,a.district as receiveArea,a.lxraddress as receiveXX from orders o left join users u on o.uuid=u.uuid LEFT JOIN address a on a.id=o.addressId" +
            "<where>" +
            "o.orderId like CONCAT('%',#{orderId},'%') "+
            // 动态时间区间条件
            "  <if test='orderTime1 != null and orderTime1 != \"\"'> " +
            "    AND o.createTime &gt;= #{orderTime1} " +
            "  </if> " +
            "  <if test='orderTime2 != null and orderTime2 != \"\"'> " +
            "    AND o.createTime &lt;= #{orderTime2} " +
            "  </if> " +
            "  <if test='downloadTime1 != null and downloadTime1 != \"\"'> " +
            "    AND o.downloadTime &gt;= #{downloadTime1} " +
            "  </if> " +
            "  <if test='downloadTime2 != null and downloadTime2 != \"\"'> " +
            "    AND o.downloadTime &lt;= #{downloadTime2} " +
            "  </if> " +
            "  <if test='getPdfTime1 != null and getPdfTime1 != \"\"'> " +
            "    AND o.getPdfTime &gt;= #{getPdfTime1} " +
            "  </if> " +
            "  <if test='getPdfTime2 != null and getPdfTime2 != \"\"'> " +
            "    AND o.getPdfTime &lt;= #{getPdfTime2} " +
            "  </if> " +
            "  <if test='trackingNo != null and trackingNo != \"\"'> " +
            "    AND o.trackingNo like CONCAT('%',#{trackingNo},'%') " +
            "  </if> " +
            "<if test='orderStatus != 0'> AND o.orderStatus = #{orderStatus} </if>" +
            "<if test='orderStatus == 0'> AND o.orderStatus != 0 </if>" +
            "<if test='isDownload != -1'> AND o.isDownload = #{isDownload} </if>" +
            "</where>" +
            "ORDER BY o.lastUpdateTime desc" +
            "</script>")
    Page<Order> getOrdersByPage(@Param("orderId") String orderId, @Param("pageNum") int pageNum, @Param("pageSize") int pageSize,@Param("orderTime1") String orderTime1,@Param("orderTime2") String orderTime2,@Param("getPdfTime1") String getPdfTime1,@Param("getPdfTime2") String getPdfTime2,@Param("downloadTime1") String downloadTime1,@Param("downloadTime2") String downloadTime2,@Param("trackingNo") String trackingNo,@Param("orderStatus") int orderStatus,@Param("isDownload") int isDownload);

    //获取订单列表 平台 状态筛查
    @Select("select o.*,u.name as uname,u.tell as utell,a.province as receivePro,a.city as receiveCity,a.district as receiveArea,a.lxraddress as receiveXX from orders o left join users u on o.uuid=u.uuid LEFT JOIN address a on a.id=o.addressId where o.orderId like CONCAT('%',#{orderId},'%') and o.createTime like CONCAT('%',#{orderTime},'%') and o.orderStatus=#{orderStatus} order by o.lastUpdateTime desc")
    Page<Order> getOrdersByPageAndStatus(@Param("orderId") String orderId, @Param("orderTime") String orderTime, @Param("orderStatus") int orderStatus, @Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

    //获取订单列表 租户
    @Select("<script>" +
            "select o.*,u.name as uname,u.tell as utell,a.province as receivePro,a.city as receiveCity,a.district as receiveArea,a.lxraddress as receiveXX from orders o left join users u on o.uuid=u.uuid LEFT JOIN address a on a.id=o.addressId" +
            "<where>" +
            "o.supplierId=#{supplierId} "+
            "AND o.orderId like CONCAT('%',#{orderId},'%') "+
            // 动态时间区间条件
            "  <if test='orderTime1 != null and orderTime1 != \"\"'> " +
            "    AND o.createTime &gt;= #{orderTime1} " +
            "  </if> " +
            "  <if test='orderTime2 != null and orderTime2 != \"\"'> " +
            "    AND o.createTime &lt;= #{orderTime2} " +
            "  </if> " +
            "  <if test='downloadTime1 != null and downloadTime1 != \"\"'> " +
            "    AND o.downloadTime &gt;= #{downloadTime1} " +
            "  </if> " +
            "  <if test='downloadTime2 != null and downloadTime2 != \"\"'> " +
            "    AND o.downloadTime &lt;= #{downloadTime2} " +
            "  </if> " +
            "  <if test='getPdfTime1 != null and getPdfTime1 != \"\"'> " +
            "    AND o.getPdfTime &gt;= #{getPdfTime1} " +
            "  </if> " +
            "  <if test='getPdfTime2 != null and getPdfTime2 != \"\"'> " +
            "    AND o.getPdfTime &lt;= #{getPdfTime2} " +
            "  </if> " +
            "  <if test='trackingNo != null and trackingNo != \"\"'> " +
            "    AND o.trackingNo like CONCAT('%',#{trackingNo},'%') " +
            "  </if> " +
            "<if test='orderStatus != 0'> AND o.orderStatus = #{orderStatus} </if>" +
            "<if test='orderStatus == 0'> AND o.orderStatus != 0 </if>" +
            "<if test='isDownload != -1'> AND o.isDownload = #{isDownload} </if>" +
            "</where>" +
            "ORDER BY o.lastUpdateTime desc" +
            "</script>")
    Page<Order> getOrdersByPageAndSupplierId(@Param("orderId") String orderId, @Param("pageNum") int pageNum, @Param("pageSize") int pageSize,@Param("orderTime1") String orderTime1,@Param("orderTime2") String orderTime2,@Param("getPdfTime1") String getPdfTime1,@Param("getPdfTime2") String getPdfTime2,@Param("downloadTime1") String downloadTime1,@Param("downloadTime2") String downloadTime2,@Param("trackingNo") String trackingNo,@Param("orderStatus") int orderStatus,@Param("isDownload") int isDownload,@Param("supplierId") int supplierId);

    //获取订单列表 租户 状态筛查
    @Select("select o.*,u.name as uname,u.tell as utell,a.province as receivePro,a.city as receiveCity,a.district as receiveArea,a.lxraddress as receiveXX from orders o left join users u on o.uuid=u.uuid LEFT JOIN address a on a.id=o.addressId where o.orderId like CONCAT('%',#{orderId},'%') and o.createTime like CONCAT('%',#{orderTime},'%') and o.orderStatus=#{orderStatus} and o.supplierId=#{supplierId} order by o.lastUpdateTime desc")
    Page<Order> getOrdersByPageAndSupplierIdAndStatus(@Param("orderId") String orderId, @Param("orderTime") String orderTime,@Param("orderStatus") int orderStatus, @Param("supplierId") int supplierId, @Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

    @Select("select o.*,s.costPrice from orderdesc o left join goods_sku s on o.skuId=s.skuId where o.orderId=#{orderId}")
    List<OrderDesc> getOrdersDescList(@Param("orderId") String orderId);

    @Select("select * from sort where parentId=#{parentId} and isDelete=0 order by level desc")
    Page<Sort> getSortByPage(@Param("pageNum") int pageNum, @Param("pageSize") int pageSize,@Param("parentId") int parentId);

    @Update("update sort set isDelete=1 where id=#{id}")
    int deleteSortById(@Param("id") int id);

    @Update("update sort set isShow=#{isShow} where id=#{id}")
    int updateSortIsshowById(@Param("id") int id,@Param("isShow") int isShow);

    @Insert("insert into sort (name,iconPic,level,parentId,type,isShow) VALUES (#{sort.name},#{sort.iconPic},#{sort.level},#{sort.parentId},#{sort.type},#{sort.isShow})")
    int addSort(@Param("sort") Sort sort);

    @Update("update sort set name=#{sort.name},iconPic=#{sort.iconPic},level=#{sort.level},parentId=#{sort.parentId},type=#{sort.type},isShow=#{sort.isShow} where id=#{sort.id}")
    int updateSortById(@Param("sort") Sort sort);

    @Select("select  * from sort where id=#{id}")
    Sort getSortDescById(@Param("id") int id);

    /**
     * 产品销售汇总
     * 按照日期筛选
     * */
    @Select("SELECT g.spuId,g.spuName,COUNT(o.spuId) AS order_count,COALESCE(SUM(o.num), 0) AS total_num,COALESCE(SUM(o.subtotal), 0) AS total_price,COALESCE(SUM(s.costPrice*o.num), 0) AS total_cost FROM goods_spu g LEFT JOIN orderdesc o ON g.spuId = o.spuId AND o.orderTime BETWEEN #{startTime} AND #{endTime} LEFT JOIN goods_sku s ON s.skuId=o.skuId LEFT JOIN orders od ON od.orderId=o.orderId and (od.orderStatus=2 or od.orderStatus=3 or od.orderStatus=4) where g.spuName LIKE CONCAT('%',#{spuName},'%') GROUP BY g.spuId, g.spuName")
    Page<GoodsSpuSale> getGoodsSpuSalePageByTime(@Param("startTime") String startTime,@Param("endTime") String endTime,@Param("spuName") String spuName,@Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

    /**
     * 产品销售汇总
     * */
    @Select("SELECT g.spuId,g.spuName,COUNT(o.spuId) AS order_count,COALESCE(SUM(o.num), 0) AS total_num,COALESCE(SUM(o.subtotal), 0) AS total_price,COALESCE(SUM(s.costPrice*o.num), 0) AS total_cost FROM goods_spu g LEFT JOIN orderdesc o ON g.spuId = o.spuId LEFT JOIN goods_sku s ON s.skuId=o.skuId LEFT JOIN orders od ON od.orderId=o.orderId and (od.orderStatus=2 or od.orderStatus=3 or od.orderStatus=4) where g.spuName LIKE CONCAT('%',#{spuName},'%') GROUP BY g.spuId, g.spuName")
    Page<GoodsSpuSale> getGoodsSpuSalePage(@Param("spuName") String spuName,@Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

    /**
     * 获取产品页
     * */
    @Select("<script>" +
            "SELECT g.*,s1.name as sort1Name,s2.name as sort2Name from goods_spu g LEFT JOIN sort s1 ON g.sort1Id = s1.id LEFT JOIN sort s2 ON g.sort2Id = s2.id" +
            "<where>" +
            "spuName like CONCAT('%',#{spuName},'%')" +
            "<if test='sort2Id != 0'> AND sort2Id = #{sort2Id} </if>" +
            "<if test='publishStatus != -1'> AND publishStatus = #{publishStatus} </if>" +
            "<if test='supplierId != 0'> AND supplierId = #{supplierId} </if>" +
            "<if test='isDiy != -1'> AND isDiy = #{isDiy} </if>" +
            "<if test='isPopular != -1'> AND isPopular = #{isPopular} </if>" +
            "<if test='isNew != -1'> AND isNew = #{isNew} </if>" +
            "<if test='isJingxuan != -1'> AND isJingxuan = #{isJingxuan} </if>" +
            "<if test='isMiaosha != -1'> AND isMiaosha = #{isMiaosha} </if>" +
            "</where>" +
            "ORDER BY level" +
            "<if test='level == 1'> DESC </if>" +
            "<if test='level == 0'> ASC </if>" +
            "</script>")
    Page<Goods> getGoodsPage(@Param("spuName") String spuName,@Param("sort2Id") int sort2Id,@Param("publishStatus") int publishStatus,@Param("supplierId") int supplierId, @Param("isDiy") int isDiy,@Param("isPopular") int isPopular, @Param("isNew") int isNew, @Param("level") int level,@Param("pageNum") int pageNum, @Param("pageSize") int pageSize,@Param("isJingxuan") int isJingxuan, @Param("isMiaosha") int isMiaosha);

    //获取所有分类
    @Select("select id,name from sort where parentId=#{parentId} and isShow=1 and isDelete=0")
    List<Sort> getAllSorts(@Param("parentId") int parentId);

    // 获取商品spu主表
    @Select("SELECT * FROM goods_spu WHERE spuId=#{id}")
    Goods getGoodsSpu(@Param("id") int id);

    //添加商品主表
    @Insert("insert into goods_spu (spuName,spuDesc,sort1Id,sort2Id,supplierId,supplierName,publishStatus,createTime,isPopular,isNew,isPromotion,level,minNum,isDiy,price,sourceId,isMiaosha,isJingxuan,isMatting,isTwo) VALUES (#{goods.spuName},#{goods.spuDesc},#{goods.sort1Id},#{goods.sort2Id},#{goods.supplierId},#{goods.supplierName},#{goods.publishStatus},#{goods.createTime},#{goods.isPopular},#{goods.isNew},#{goods.isPromotion},#{goods.level},#{goods.minNum},#{goods.isDiy},#{goods.price},#{goods.sourceId},#{goods.isMiaosha},#{goods.isJingxuan},#{goods.isMatting},#{goods.isTwo})")
    int addGoodsSpu(@Param("goods") Goods goods);

    //修改商品主表
    @Update("update goods_spu set spuName=#{goods.spuName},spuDesc=#{goods.spuDesc},sort1Id=#{goods.sort1Id},sort2Id=#{goods.sort2Id},supplierId=#{goods.supplierId},supplierName=#{goods.supplierName},publishStatus=#{goods.publishStatus},updateTime=#{goods.updateTime},isPopular=#{goods.isPopular},isNew=#{goods.isNew},isPromotion=#{goods.isPromotion},level=#{goods.level},minNum=#{goods.minNum},isDiy=#{goods.isDiy},price=#{goods.price},sourceId=#{goods.sourceId},isMiaosha=#{goods.isMiaosha},isJingxuan=#{goods.isJingxuan},isMatting=#{goods.isMatting},isTwo=#{goods.isTwo} where spuId=#{goods.spuId}")
    int editGoodsSpu(@Param("goods") Goods goods);

    //按分类获取商品图片列表
    @Select("select * from goods_spu_images where type=#{type} and spuId=#{spuId} order by level desc")
    List<GoodsImage> getGoodsImage(@Param("type") int type,@Param("spuId") int spuId);

    //新增商品图片表
    @Insert("insert into goods_spu_images (picUrl,type,level,spuId) VALUES (#{picUrl},#{type},#{level},#{spuId})")
    int addGoodsImage(@Param("picUrl") String picUrl,@Param("type") int type,@Param("level") int level,@Param("spuId") int spuId);

    //修改商品图片
    @Update("update goods_spu_images set picUrl=#{picUrl},level=#{level} where id=#{id}")
    int editGoodsImage(@Param("picUrl") String picUrl,@Param("level") int level,@Param("id") int id);

    //删除商品图片表
    @Delete("delete from goods_spu_images where id=#{id}")
    int deleteGoodsImage(@Param("id") int id);

    //获取商品主图
    @Select("select picUrl from goods_spu_images where type=1 and spuId=#{spuId} order by level desc limit 0,1")
    String getGoodsMainPic(@Param("spuId") int spuId);

    //修改商品主图
    @Update("update goods_spu set goodsPic=#{goodsPic} where spuId=#{id}")
    int editGoodsMainPic(@Param("id") int id,@Param("goodsPic") String goodsPic);

    //获取商品图片详情
    @Select("select * from goods_spu_images where id=#{id}")
    GoodsImage getGoodsImageById(@Param("id") int id);

    //获取商品规格列表
    @Select("select g.skuId,g.salesPrice,g.underlinedPrice,g.costPrice,g.indexes,g.own_spec,g.enable,g.skuPic,s.stock from goods_sku g left join goods_sku_stock s on g.skuId=s.skuId where g.spuId=#{spuId}")
    Page<GoodsSku> getGoodsSkuList(@Param("spuId") int spuId,@Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

    //修改sku
    @Update({
            "<script>",
            "update goods_sku set salesPrice=#{salesPrice},underlinedPrice=#{underlinedPrice},costPrice=#{costPrice},enable=#{enable},skuPic=#{skuPic} ",
            "WHERE skuId IN ",
            "<foreach item='item' index='index' collection='ids' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</script>"
    })
    int editGoodsSku(@Param("ids") List<Integer> ids,@Param("salesPrice") double salesPrice,@Param("underlinedPrice") double underlinedPrice,@Param("costPrice") double costPrice,@Param("enable") int enable,@Param("skuPic") String skuPic);

    //修改库存
    @Update({
            "<script>",
            "update goods_sku_stock set stock=#{stock} ",
            "WHERE skuId IN ",
            "<foreach item='item' index='index' collection='ids' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</script>"
    })
    int editStock(@Param("ids") List<Integer> ids,@Param("stock") int stock);

    //获取sku详情
    @Select("select g.*,s.stock from goods_sku g left join goods_sku_stock s on g.skuId=s.skuId where g.skuId=#{skuId}")
    GoodsSku getGoodsSkuById(@Param("skuId") int id);
    
    //获取spec信息
    @Select("select spec_template from goods_spu_detail where spuId=#{spuId};")
    String getGoodsSpecById(@Param("spuId") int id);

    //跟新或者新增spec信息
    @Insert("insert into goods_spu_detail (spuId,spec_template) values (#{spuId},#{spec_template})")
    int addGoodsSpec(@Param("spuId") int id,@Param("spec_template") String spec_template);

    @Update("update goods_spu_detail set spec_template=#{spec_template} where spuId=#{spuId}")
    int updateGoodsSpec(@Param("spuId") int id,@Param("spec_template") String spec_template);

    //删除之前全部的sku
    @Delete("delete from goods_sku where spuId=#{spuId}")
    int deleteGoodsSku(@Param("spuId") int id);

    @Delete("DELETE FROM goods_sku_stock WHERE skuId IN (SELECT skuId FROM goods_sku WHERE spuId =#{spuId})")
    int deleteGoodsSkuStock(@Param("spuId") int id);

    @Select("select * from logisticsconfig where supplierId=#{supplierId}")
    Logistics getLogisticsById(@Param("supplierId") int supplierId);

    @Select("select o.*,a.province as prov,a.city,a.district as area,a.lxraddress as xx from orders o LEFT JOIN address a on a.id=o.addressId where o.orderId=#{orderId}")
    Order getOrderById(@Param("orderId") String id);

    //发货
    @Update("update orders set isGetTrackingNo=1,orderStatus=#{orderStatus},trackingNo=#{trackingNo},deliveryTime=#{deliveryTime},wlOrderId=#{wlOrderId},lastUpdateTime=#{deliveryTime} where orderId=#{orderId}")
    int fahuo(@Param("orderId") String id,@Param("trackingNo") String trackingNo,@Param("deliveryTime") String deliveryTime,@Param("orderStatus") int orderStatus,@Param("wlOrderId") String wlOrderId);

    //取消发货
    @Update("update orders set isGetTrackingNo=0,orderStatus=2,trackingNo=null,deliveryTime=null,wlOrderId=null where orderId=#{orderId}")
    int quxiaofahuo(@Param("orderId") String id);

    //获取打印base64
    @Update("update orders set base64EncodeContent=#{base64EncodeContent},isPrint=1 where orderId=#{orderId}")
    int setPrintPic(@Param("orderId") String id,@Param("base64EncodeContent") String base64EncodeContent);

    @Update("update orders set base64EncodeContent=#{base64EncodeContent},isPrint=1,lastUpdateTime=#{lastUpdateTime} where orderId=#{orderId}")
    int setPrintPic2(@Param("orderId") String id,@Param("base64EncodeContent") String base64EncodeContent,@Param("lastUpdateTime") String lastUpdateTime);

    //先查看supplier是否配置过
    @Select("select supplierId from logisticsconfig where supplierId=#{supplierId}")
    String checkSupplierIsAdd(@Param("supplierId") int supplierId);

    //新增物流配置信息
    @Insert("insert into logisticsconfig (supplierId,jt_apiAccount,jt_privateKey,jt_customerCode,jt_orderPassword,sendAddress,sendName,sendTell,sendProv,sendCity,sendArea) VALUES (#{logistics.supplierId},#{logistics.jt_apiAccount},#{logistics.jt_privateKey},#{logistics.jt_customerCode},#{logistics.jt_orderPassword},#{logistics.sendAddress},#{logistics.sendName},#{logistics.sendTell},#{logistics.sendProv},#{logistics.sendCity},#{logistics.sendArea})")
    int addLogisticsConfig(@Param("logistics") Logistics logistics);

    //修改物流配置信息
    @Update("update logisticsconfig set jt_apiAccount=#{logistics.jt_apiAccount},jt_privateKey=#{logistics.jt_privateKey},jt_customerCode=#{logistics.jt_customerCode},jt_orderPassword=#{logistics.jt_orderPassword},sendAddress=#{logistics.sendAddress},sendName=#{logistics.sendName},sendTell=#{logistics.sendTell},sendProv=#{logistics.sendProv},sendCity=#{logistics.sendCity},sendArea=#{logistics.sendArea} where supplierId=#{logistics.supplierId}")
    int updateLogisticsConfig(@Param("logistics") Logistics logistics);

    //获取边框图片资源包
    @Select("select * from template_package where isDelete=0")
    Page<TemplatePackage> getTemplatePackageByPage(@Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

    @Select("select * from template_package where isDelete=0")
    List<TemplatePackage> getTemplatePackages();

    //新增边框图片资源包
    @Insert("insert into template_package (name) VALUES (#{name})")
    int addTemplatePackage(@Param("name") String name);

    //修改边框图片资源包
    @Update("update template_package set name=#{name} where id=#{id}")
    int updateTemplatePackage(@Param("name") String name, @Param("id") int id);

    //删除边框图片分类
    @Update("update template_package set isDelete=1 where id=#{id}")
    int delTemplatePackage(@Param("id") int id);

    //获取边框图片分类
    @Select("select * from template_sort where isDelete=0 and parentId=#{parentId} and type=#{type} order by level desc")
    Page<TemplateSort> getTemplateImageSortList(@Param("pageNum") int pageNum, @Param("pageSize") int pageSize ,@Param("parentId") int parentId,@Param("type") int type);

    //新增边框图片分类
    @Insert("insert into template_sort (sortName,isShow,level,parentId,type) VALUES (#{sortName},#{isShow},#{level},#{parentId},#{type})")
    int addTemplateImageSort(@Param("sortName") String sortName,@Param("isShow") int isShow, @Param("level") int level,@Param("parentId") int parentId,@Param("type") int type);

    //修改边框图片分类
    @Update("update template_sort set sortName=#{sortName},isShow=#{isShow},level=#{level} where id=#{id}")
    int updateTemplateImageSort(@Param("sortName") String sortName,@Param("isShow") int isShow, @Param("level") int level, @Param("id") int id);

    //删除边框图片分类
    @Update("update template_sort set isDelete=1 where id=#{id}")
    int delTemplateImageSort(@Param("id") int id);

    //修改边框图片分类状态
    @Update("update template_sort set isShow=#{isShow} where id=#{id}")
    int updateTemplateImageSortStatus(@Param("isShow") int isShow,@Param("id") int id);


    @Select("select * from template_sort_image where sortId=#{sortId} and type=#{type} order by level desc")
    Page<TemplateImage> getTempImageByPage(@Param("pageNum") int pageNum, @Param("pageSize") int pageSize ,@Param("sortId") int sortId,@Param("type") int type);

    //新增边框图片分类
    @Insert("insert into template_sort_image (sortId,templatePic,level,type) VALUES (#{sortId},#{templatePic},#{level},#{type})")
    int addTempImage(@Param("templatePic") String templatePic,@Param("sortId") int sortId, @Param("level") int level, @Param("type") int type);

    //修改边框图片分类
    @Update("update template_sort_image set templatePic=#{templatePic},level=#{level} where id=#{id}")
    int editTempImage(@Param("templatePic") String templatePic, @Param("level") int level, @Param("id") int id);

    //删除边框图片分类
    @Delete("delete from template_sort_image where id=#{id}")
    int delTempImage(@Param("id") int id);

    @Select("select * from template_sort_image where id=#{id}")
    TemplateImage getTempImageDesc(@Param("id") int id);

    @Select("select oid from orderdesc where orderId=#{orderId}")
    List<Integer> getAllOidByOrderId(@Param("orderId") String orderId);

    @Select("select * from editimage where orderId=#{orderId}")
    List<EditImage> getEditImageById(@Param("orderId") String orderId);

    @Select("select * from editimage where oid=#{oid}")
    List<EditImage> getEditImageByOid(@Param("oid") int oid);

    @Select("SELECT isTwo from goods_spu where spuId=(SELECT spuId FROM orderdesc where oid=#{oid})")
    int getGoodsIsTwo(@Param("oid") int oid);

    @Insert("insert into pdflogs (orderId,oid,pdfUrl) values (#{orderId},#{oid},#{pdfUrl})")
    int addPdfLogs(@Param("oid") int oid,@Param("orderId") String orderId,@Param("pdfUrl") String pdfUrl);

    @Select("select pdfUrl from pdflogs where orderId=#{orderId}")
    List<String> getPdfByOrderId(@Param("orderId") String orderId);

    @Select("select IFNULL(SUM(num), 0) from editimage where oid=#{oid}")
    int getEditImagesNum(@Param("oid") int oid);

    @Update("update marketing_poster set imageUrl=#{imageUrl}")
    int saveIndexMarketingPoster(@Param("imageUrl") String imageUrl);

    //获取当前订单已经申请多少退款
    @Select("select COALESCE(SUM(refundTotal), 0) from orders_refund where orderId=#{orderId} and (status=1 or status=3)")
    int getTotalRefundAmontByOrderId(@Param("orderId") String orderId);

    @Insert("insert into orders_refund(out_refund_no,orderId,refundTotal,orderTotal,status,refund_id,createTime,out_trade_no) values (#{ordersRefund.out_refund_no},#{ordersRefund.orderId},#{ordersRefund.refundTotal},#{ordersRefund.orderTotal},#{ordersRefund.status},#{ordersRefund.refund_id},#{ordersRefund.createTime},#{ordersRefund.out_trade_no})")
    int saveRefunLogsToSql(@Param("ordersRefund") OrdersRefund ordersRefund);

    @Update("update orders set afterStatus=#{afterStatus} where orderId=#{orderId}")
    int updateOrderAfterStatus(@Param("orderId") String orderId,@Param("afterStatus") int afterStatus);

    @Update("update orders_refund set status=#{status},refundTime=#{refundTime} where refund_id=#{refund_id}")
    int updaterefundOrderAfterStatus(@Param("refund_id") String refund_id,@Param("status") int status,@Param("refundTime") String refundTime);

    @Select("select * from orders_refund where refund_id=#{refund_id}")
    OrdersRefund getOrdersRefundDesc(@Param("refund_id") String refund_id);

    @Insert("insert into wuliu(trackingNo,trackingDesc,scanTime,scanType) values (#{trackingNo},#{trackingDesc},#{scanTime},#{scanType})")
    int addWuliuGuiji(@Param("trackingNo") String trackingNo,@Param("trackingDesc") String trackingDesc,@Param("scanTime") String scanTime,@Param("scanType") String scanType);

    @Select("select count(1) from wuliu where trackingNo=#{trackingNo} and scanTime=#{scanTime} and scanType=#{scanType}")
    int getWuliuGuijiNum(@Param("trackingNo") String trackingNo,@Param("scanTime") String scanTime,@Param("scanType") String scanType);

    @Select("select * from orders where trackingNo=#{trackingNo}")
    Order getOrderByTrackingNo(@Param("trackingNo") String trackingNo);

    @Select("select * from wuliu where trackingNo=#{trackingNo}")
    List<WuliuDesc> getWuliuList(@Param("trackingNo") String trackingNo);

    @Update("update orders_wait set status=1,caozuotime=#{caozuotime} where status=0 and createtime < UNIX_TIMESTAMP(NOW() - INTERVAL 4 HOUR) * 1000")
    void updateWaitOrderStatus(@Param("caozuotime") long caozuotime);

    @Select("select * from orders_wait where caozuotime=#{caozuotime} and status=1")
    List<WaitOrder> getWaitOrders(@Param("caozuotime") long caozuotime);

    @Update("update orders set getPdfTime=#{getPdfTime},lastUpdateTime=#{getPdfTime},pdfNum=#{pdfNum},pdfSize=#{pdfSize},pdfYeshu=#{pdfYeshu},pdfUrl=#{pdfUrl} where orderId=#{orderId}")
    int updateOrdersBaodan(@Param("getPdfTime") String getPdfTime,@Param("pdfNum") int pdfNum,@Param("pdfSize") String pdfSize,@Param("pdfYeshu") int pdfYeshu,@Param("pdfUrl") String pdfUrl,@Param("orderId") String orderId);

    @Delete("delete from orders_wait where orderId=#{orderId} and type=#{type}")
    int delWaitOrder(@Param("orderId") String orderId,@Param("type") int type);

    @Select("select orderId from orders where systemId=#{systemId}")
    List<String> getAllOrderIdBySystemId(String systemId);

    @Update("update orders set downloadTime=#{downloadTime},lastUpdateTime=#{downloadTime},isDownload=1 where orderId=#{orderId}")
    void updateOrdersDownloadStatus(@Param("downloadTime") String downloadTime,@Param("orderId") String orderId);

    @Select("<script>" +
            "SELECT count(1) from orders" +
            "<where>" +
            "payStatus=1 AND orderStatus=2" +
            "<if test='accountType != 0'> AND supplierId = #{supplierId} </if>" +
            "</where>" +
            "</script>")
    int getAllUnshippedOrdersNum(@Param("accountType") int accountType,@Param("supplierId") int supplierId);

    @Select("<script>" +
            "SELECT count(1) from orders" +
            "<where>" +
            "payStatus=1 AND (orderStatus=3 or orderStatus=4)" +
            "<if test='accountType != 0'> AND supplierId = #{supplierId} </if>" +
            " AND deliveryTime LIKE CONCAT('%',#{yesterday},'%')"+
            "</where>" +
            "</script>")
    int getYesterdaySendOrdersNum(@Param("accountType") int accountType,@Param("supplierId") int supplierId,@Param("yesterday") String yesterday);

    @Select("select * from orderdesc where oid=#{oid}")
    OrderDesc getOrderDesc(@Param("oid") int oid);

    @Select("SELECT SUM(num) AS totalNum FROM editimage where oid=#{oid}")
    int getPdfNum(@Param("oid") int oid);

    /**
     * 获取此产品生成pdf封面所需图
     * */
    @Select("select picUrl from goods_spu_images where type=#{type} and spuId=(select spuId from orderdesc where oid=#{oid}) limit 0,1")
    String getPdfCoverTemp(@Param("type") int type,@Param("oid") int oid);
}
